package top.xuwuqiang.study.jacawebjdbc.dao;

import top.xuwuqiang.study.jacawebjdbc.entity.Tbmovie;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbMovieDAO {
    public static int insert(Tbmovie movie)throws Exception{
        Connection conn=DBHelper.getConnection();
//        带参数的sql语句
        String sql="insert into tb_movie(movie,info,score) values(?,?,?)";
        //预处理语句
        PreparedStatement ps=conn.prepareStatement(sql);
        //设置参数
        ps.setString(1,movie.getMovie());
        ps.setString(2,movie.getInfo());
        ps.setInt(3,movie.getScore());
//        执行sql语句并获取返回影响行数
        int result=ps.executeUpdate();


        conn.close();
        return result;
    }

    public static List<Tbmovie>query()throws Exception{

        List<Tbmovie>list=new ArrayList<>();
        Connection conn=DBHelper.getConnection();
        String sql="select * from tb_movie";
        PreparedStatement ps=conn.prepareStatement(sql);
        //读取查询结果
        ResultSet rs=ps.executeQuery();

        while(rs.next()){
            Tbmovie movie=new Tbmovie();

            movie.setId(rs.getInt("id"));

            movie.setMovie(rs.getString("movie"));

            movie.setInfo(rs.getString("info"));

            movie.setScore(rs.getInt("score"));

            movie.setCreated(rs.getTimestamp("created"));

            list.add(movie);
        }


        conn.close();
        return list;
    }

    public static int delete(Integer id)throws Exception {
        Connection conn = DBHelper.getConnection();
        String sql = "delete from tb_movie where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();

        return i;

    }
}
